package com.cfs.daoimpl.sys;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.util.ExcelUtil;
import com.cfs.po.sys.*;

/**
 * 
 * 
 * @author SDZJ
 * 
 */
public class CascDao extends Dao {

	// private static Map<String, Dict> dMap = new HashMap<String, Dict>();
	// private static Map<String, Dict> tMap = null;
	private RowMapper<KV> mapper = new RowMapper<KV>() {

		@Override
		public KV mapRow(ResultSet rs, int arg1) throws SQLException {

			KV kv = new KV(rs.getInt("EKEY"), rs.getString("EVALUE"));

			String namePk = rs.getString("NAME") + "_" + rs.getInt("PK");
			// Dict d = tMap.get(namePk);
			// if(d == null){
			// d = new Dict();
			// d.setName(namePk);
			// d.setKvs(new ArrayList<KV>());
			// tMap.put(namePk, d);
			// }
			// d.add(kv);

			Dict d = new Dict();
			d.setName(namePk);
			d.setKvs(new ArrayList<KV>());
			d.add(kv);

			return kv;
		}
	};

	public CascDao() {
	}

	/**
	 * 初始化字典缓存
	 * 
	 * @param c
	 * @return
	 */
	// public synchronized void initCasc() {
	// if(tMap==null){
	// tMap = new HashMap<String, Dict>();
	// jdbcTemplate.query(DB_SQL.CASC_QUERY_ALL, mapper);
	//			
	// Map<String, Dict> p = dMap;
	// dMap = tMap;
	// tMap = null;
	// p.clear();
	// }
	//
	// System.out.println("Init Casc: ......OK");
	// }

	/**
	 * 
	 * @param name
	 * @param pk
	 * @return
	 */
	public Dict getByNamePk(String name, int pk) {
		// if(dMap.size()==0){
		// initCasc();
		// }
		// return dMap.get(name + "_" + pk);

		Dict d = new Dict();
		d.setName(name);
		d.setPk(pk > 0 ? pk : null);

		List<KV> kvs = pk > 0 ? jdbcTemplate.query(DB_SQL.CASC_QUERY_BY_NAME_PK, new Object[] { name, pk }, mapper) : jdbcTemplate.query(DB_SQL.CASC_QUERY_BY_NAME, new Object[] { name }, mapper);
		d.setKvs(kvs);

		return d;
	}

	/**
	 * 根据name, ekey 获取 evalue(名称)
	 * 
	 * @param name
	 * @param ekey
	 * @return
	 */
	public String getByNameEkey(String name, int ekey) {
		List<String> strs = jdbcTemplate.queryForList("SELECT EVALUE FROM TB_CASC WHERE NAME = ? AND EKEY = ?",new Object[]{name,ekey},String.class);
		return strs.size()==0?"":strs.get(0);
	}

	/**
	 * 
	 * @param name
	 * @param pks
	 * @return
	 */
	public Dict[] getByNamePks(String name, long[] pks) {

		if (pks == null || pks.length < 1) {
			return null;
		}

		Dict[] da = new Dict[pks.length];
		for (int x = 0; x < pks.length; x++) {
			da[x] = this.getByNamePk(name, (int) pks[x]);
		}

		return da;
	}

	/**
	 * 
	 * @return
	 */
	public List<String> listNames() {
		return jdbcTemplate.query(DB_SQL.CASC_QUERY_ALL_NAMES, new RowMapper<String>() {

			@Override
			public String mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getString(1);
			}
		});
	}

	/**
	 * 
	 * 
	 * @param name
	 * @return
	 */
	public int deleteByName(String name) {
		try {
			return jdbcTemplate.update(DB_SQL.CASC_DEL_BY_NAME, name);
		} finally {
			// initCasc();
		}
	}

	/**
	 * 
	 * 
	 * @param name
	 * @return
	 */
	public int deleteByNamePk(String name, int pk) {
		try {
			return jdbcTemplate.update(DB_SQL.CASC_DEL_BY_NAME_PK, name, pk);
		} finally {
			// initCasc();
		}
	}

	/**
	 * 
	 * @param name
	 * @param key
	 * @return
	 */
	public int deleteByNamePkKey(String name, int pk, int key) {
		try {
			return jdbcTemplate.update(DB_SQL.CASC_DEL_BY_NAME_PK_KEY, name, pk, key);
		} finally {
			// initCasc();
		}
	}

	public int add(Dict d) {

		List<Object[]> list = new ArrayList<Object[]>();
		for (KV kv : d.getKvs()) {
			list.add(new Object[] { d.getPk(), d.getName(), kv.getK(), kv.getV(), "" });
		}

		int[] ia = jdbcTemplate.batchUpdate(DB_SQL.CASC_INS, list);

		// initCasc();

		return ia.length;
	}

	/**
	 * 
	 * @param d
	 * @return
	 */
	public int update(Dict d) {
		String sql = DB_SQL.CASC_QUERY_BY_NAME_PK;
		Object[] pa = { d.getName(), d.getPk() };
		if (d.getPk() == null || d.getPk() <= 0) {
			d.setPk(null);
			sql = DB_SQL.CASC_QUERY_BY_NAME;
			pa = new Object[] { d.getName() };
		}

		List<Long> la = jdbcTemplate.query(sql, pa, new RowMapper<Long>() {
			@Override
			public Long mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getLong(1);
			}
		});

		List<Object[]> uplist = new ArrayList<Object[]>();
		List<Object[]> list = new ArrayList<Object[]>();

		for (KV kv : d.getKvs()) {
			if (la.size() > 0) {
				uplist.add(new Object[] { d.getPk(), d.getName(), kv.getK(), kv.getV(), "", la.remove(0) });
			} else {
				list.add(new Object[] { d.getPk(), d.getName(), kv.getK(), kv.getV(), "" });
			}
		}

		int[] iu = jdbcTemplate.batchUpdate(DB_SQL.CASC_UPD_BY_ID, uplist);
		int[] ia = jdbcTemplate.batchUpdate(DB_SQL.CASC_INS, list);
		int[] idel = {};

		if (la.size() > 0) {
			List<Object[]> dList = new ArrayList<Object[]>();
			for (Long l : la) {
				dList.add(new Object[] { l });
			}
			idel = jdbcTemplate.batchUpdate(DB_SQL.CASC_DEL_BY_ID, dList);
		}

		// initCasc();

		return iu.length + ia.length + idel.length;
	}

	/**
	 * 导入省市县（要拆分）
	 * 
	 * @param fn
	 * @return
	 */
	public int impShengShiXian(String fn) {
		List<List<String>> list = new ExcelUtil().read(fn);

		List<Object[]> pl = new LinkedList<Object[]>();
		for (List<String> row : list) {
			Object[] p = new Object[5];
			int key = Integer.parseInt(row.get(0));
			if (key % 10000 == 0) {
				// p[0] = key%10000;
				p[1] = "SHENG";
				p[2] = key;
				p[3] = row.get(1);

			} else if (key % 100 == 0) {
				p[0] = (key / 10000) * 10000;
				p[1] = "SHENG_SHI";
				p[2] = key;
				p[3] = row.get(1);

			} else {
				p[0] = 100 * (key / 100);
				p[1] = "SHENG_SHI_XIAN";
				p[2] = key;
				p[3] = row.get(1);

			}
			// p[4] = "";

			pl.add(p);
		}

		return jdbcTemplate.batchUpdate(DB_SQL.CASC_INS, pl).length;
	}

	/**
	 * 查询品牌和品类
	 * 
	 * @return
	 */
//	public BrandDict[] getBrandAndCate() {
//		//String sql = "SELECT PK,NAME,EKEY,EVALUE,MEMO,ID FROM TB_CASC WHERE MEMO='BRAND_CATE'";
//		String sql = "SELECT TB.ID AS ID,TB.PK AS PK,TB.EKEY AS CATEKEY,TB.EVALUE AS CATEVALUE,TBB.EVALUE AS BRAND,TB.NAME AS NAME FROM (SELECT * FROM TB_CASC WHERE NAME='BRAND_CATEGORY') TB "+
//                      " LEFT JOIN (SELECT * FROM TB_CASC WHERE NAME='BRAND') TBB ON TBB.EKEY=TB.PK";
//		List<BrandDict> list = jdbcTemplate.query(sql, new RowMapper<BrandDict>() {
//
//			@Override
//			public BrandDict mapRow(ResultSet rs, int index) throws SQLException {
//				BrandDict b = new BrandDict();
//				b.setId(rs.getLong(1));
//				b.setBrandKey(rs.getInt(2));
//				b.setBrandValue(rs.getString(5));
//				b.setCategoryKey(rs.getInt(3));
//				b.setCategoryValue(rs.getString(4));
//				b.setName(rs.getString(6));
//				return b;
//			}
//		});
//		BrandDict[] brands = new BrandDict[list.size()];
//		for (int i = 0; i < list.size(); i++) {
//            System.out.println("-----------------------i=="+i+"------"+list.get(i).getBrandKey());
//            System.out.println("-----------------------i=="+i+"------"+list.get(i).getBrandValue());
//            System.out.println("-----------------------i=="+i+"------"+list.get(i).getCategoryKey());
//            System.out.println("-----------------------i=="+i+"------"+list.get(i).getCategoryValue());
//			brands[i] = list.get(i);
//		}
//     
//		return brands;
//	}

//	/**
//	 * 查询竞品品牌和品类
//	 * 
//	 * @return
//	 */
//	public BrandDict[] getCompeteBrandAndCate(String competebrand,String competecategory) {
//		BrandDict[] brands = null;
//		String sql = "SELECT TB.ID AS ID,TB.PK AS PK,TB.EKEY AS CATEKEY,TB.EVALUE AS CATEVALUE,TBB.EVALUE AS BRAND,TB.NAME AS NAME FROM (SELECT * FROM TB_CASC WHERE NAME='"+competecategory+"') TB "+
//                      " LEFT JOIN (SELECT * FROM TB_CASC WHERE NAME='"+competebrand+"') TBB ON TBB.EKEY=TB.PK  ORDER BY TBB.ID";
//		List<BrandDict> list = jdbcTemplate.query(sql, new RowMapper<BrandDict>() {
//			@Override
//			public BrandDict mapRow(ResultSet rs, int index) throws SQLException {
//				BrandDict b = new BrandDict();
//				b.setId(rs.getLong(1));
//				b.setBrandKey(rs.getInt(2));
//				b.setBrandValue(rs.getString(5));
//				b.setCategoryKey(rs.getInt(3));
//				b.setCategoryValue(rs.getString(4));
//				b.setName(rs.getString(6));
//				return b;
//			}
//		});
//		if(list != null && list.size() > 0){
//			brands = new BrandDict[list.size()];
//			for (int i = 0; i < list.size(); i++) {
//				brands[i] = list.get(i);
//			}
//		}
//		return brands;
//	}

}
